package com.cz.fra.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cz.fra.dao.AccountDao;
import com.cz.fra.dao.EntityNotFoundException;
import com.cz.fra.dao.PersistenceException;
import com.cz.fra.entity.User;
import com.cz.fra.utils.DBUtil;

public class JdbcAccountDao implements AccountDao {

	private DBUtil dbUtil = new DBUtil();
	
	@Override
	public String addAccount(User user) throws PersistenceException,
			EntityNotFoundException {
		String add_user_sql = "insert into "+ DBUtil.databaseName +".frs_user (username, password,email,register_date,address)"
				+"values('"+user.getUsername()+"','"+user.getPassword()+"', '"+user.getEmail()+"',"
				+" CURRENT_TIMESTAMP,'"+user.getAddress()+"')";
		dbUtil.getConnection();
		if(dbUtil.executeUpdate(add_user_sql)){
			return "success";	
		}else {
			return "failed";	
		}
	}

	@Override
	public boolean checkUserName(User user) throws PersistenceException,
			EntityNotFoundException {
		String username_validate_sql = "select * from "+ DBUtil.databaseName +".frs_user where username = '"+user.getUsername()+"'";
		ResultSet rs = null;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(username_validate_sql);
			if(rs.next()){
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return true;
	}

	@Override
	public List<String> getUsernameList() throws PersistenceException,
			EntityNotFoundException {
		String get_username_list_sql = "select username from "+ DBUtil.databaseName +".frs_user";
		ResultSet rs = null;
		dbUtil.getConnection();
		List<String> usernameList = new ArrayList<String>();
		try {
			rs = dbUtil.executeQuery(get_username_list_sql);
			while(rs.next()){
				usernameList.add(rs.getString("username"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return usernameList;
	}

	@Override
	public int getUserIdByUsername(String username)
			throws PersistenceException, EntityNotFoundException {
		String get_user_id_by_user_name = "select user_id from "+ DBUtil.databaseName +".frs_user where username = '"+username+"'";
		ResultSet rs = null;
		dbUtil.getConnection();
		int userId = -1;
		try {
			rs = dbUtil.executeQuery(get_user_id_by_user_name);
			if(rs.next()){
				userId = rs.getInt("user_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return userId;
	}

	@Override
	public List<User> getUserList() throws PersistenceException,
			EntityNotFoundException {
		String get_user_list_sql = "select user_id, username, profile_img from "+ DBUtil.databaseName +".frs_user";
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		User user = null;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_user_list_sql);
			while(rs.next()){
				user = new User();
				user.setUserid(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				user.setProfileImg(rs.getString("profile_img"));
				userList.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return userList;
	}

}
